﻿' Created Date : 02-May-2012
' Last Updated : 03-May-2012
' 02-May-2012
' API Testing Completed.
'
' 03-May-2012
' SearchData Function created to return the result.
' FormatTable function created to return the required information in a datatable from result object.
' 
' 04-May-2012
' Insert records into database table.
'
' 06-May-2012
' ODBC Function to insert the records.

Imports System.Configuration
Imports DnBDataCollector.DnBDirect
Imports System.Data.Odbc
Public Class DnBData
    Dim APIKey As String
    Dim CountryId As Long
    Dim Client As DnBDirect.DnB10Client = Nothing
    Dim LastSearchMode As String = ""
    Dim LastSearchString As String = ""
    Dim mNextOffset As Integer = 0
    Dim mCurrentOffset As Integer = 0
    Dim mLastHits As Integer = 0
    Dim bizDataDataset As New BizDataDataSetTableAdapters.bizDataTableAdapter
    Dim C, C1, C2, C3 As Integer
    Dim S As String
    Dim T As Integer


    Public ReadOnly Property CurrentOffset() As Integer
        Get
            Return mCurrentOffset
        End Get
    End Property

    Public Property NextOffset() As Integer 'why is this 1600?
        Get
            Return mNextOffset
        End Get
        Set(value As Integer)
            mNextOffset = value
        End Set
    End Property

    Private _insertedCount As Integer
    Public Property InsertedCount() As Integer 'why is this 1600?
        Get
            Return _insertedCount
        End Get
        Set(value As Integer)
            _insertedCount = value
        End Set
    End Property

    Private _UpdatedCount As Integer
    Public Property UpdatedCount() As Integer 'why is this 1600?
        Get
            Return _UpdatedCount
        End Get
        Set(value As Integer)
            _UpdatedCount = value
        End Set
    End Property

    Public Sub New()
        APIKey = ConfigurationManager.AppSettings("APIKey")
        Client = New DnB10Client
    End Sub

    Public Function SearchData(ByVal SearchMode As String, ByVal SearchString As String, Optional ByVal Hits As Integer = 1) As AdvancedCompanySearchResponse
        Try
            'what does 1 mean ?, 'where did 10003 come from? 200?
            Dim o As New AdvancedCompanySearchRequest
            o.bal = New AdvancedCompanySearchRequestBal
            With o.bal
                ' Define the Offset.
                If SearchString <> LastSearchString OrElse SearchMode <> LastSearchMode Then
                    'NextOffset = rowStartSearch
                    mLastHits = Hits

                    LastSearchString = SearchString
                    LastSearchMode = SearchMode
                    If SearchMode <> "Zipcode" Then
                        NextOffset = 0
                    End If
                End If
                mCurrentOffset = NextOffset
                .hitOffsetSpecified = True
                .hitOffset = NextOffset

                .maxRecords = mLastHits
                .maxRecordsSpecified = True
                .sortDirection = SortDirectionTypes.Ascending

                .location = New CompanyLocation
                .location.countryId = New Long() {76}
                Select Case SearchMode
                    Case "Zipcode"
                        Dim ZipRange(0) As DnBDirect.Range
                        ZipRange(0) = New DnBDirect.Range
                        Dim zipInt As Integer = SearchString '.Split(" ")(1)
                        ZipRange(0).from = zipInt
                        ZipRange(0).to = zipInt
                        .location.usZipCodeRange = ZipRange
                    Case "State"
                        .location.usStateCanadaProvince = New String() {SearchString}
                    Case "City"
                        .location.city = New String() {SearchString}
                End Select
            End With

            Dim res As New AdvancedCompanySearchResponse
            res = Client.AdvancedCompanySearch(APIKey, "", "", o)


            '' Insert Records only if there are records retrieved
            If res.return.companies.hits > 0 Then
                'InsertRecordsODBC(res)
                InsertRecordsViaTableAdapter(res)
            End If

            NextOffset += Val(res.return.companies.hits)

            If SearchMode = "Zipcode" Then
                Dim zipDataset As New ZipCodeDataSetTableAdapters.ZipTableTableAdapter
                zipDataset.UpdateZipTable(NextOffset, res.return.companies.totalHits, SearchString)
            End If


            Return res
        Catch ex As Exception
            Throw
        Finally
        End Try
    End Function

    Public Function FormatTable(ByVal res As AdvancedCompanySearchResponse) As DataTable
        Dim dt As New DataTable
        Try
            dt.Columns.Add("CompanyId", GetType(String))
            dt.Columns.Add("CompanyName", GetType(String))
            dt.Columns.Add("Address1", GetType(String))
            dt.Columns.Add("City", GetType(String))
            dt.Columns.Add("StateOrProvince", GetType(String))
            dt.Columns.Add("PostalCode", GetType(String))
            dt.Columns.Add("Phone", GetType(String))

            Dim dr As DataRow
            For Each co As CompanySearchResultHit In res.return.companies.hit
                dr = dt.NewRow
                dr("CompanyId") = co.companyResults.companyId
                dr("CompanyName") = co.companyResults.companyName
                dr("Address1") = co.companyResults.address1
                dr("City") = co.companyResults.city
                dr("StateOrProvince") = co.companyResults.stateOrProvince
                dr("PostalCode") = co.companyResults.postalCode
                dr("Phone") = co.companyResults.hqPhone

                dt.Rows.Add(dr)
            Next
            Return dt
        Catch ex As Exception
            Throw
        Finally
        End Try
    End Function

    Public Function PopulateResultsDataTable(ByVal res As AdvancedCompanySearchResponse, resDataTable As ResultDataSet.ResultDataTableDataTable) As ResultDataSet.ResultDataTableDataTable
        If Not IsNothing(res.return.companies.hit) Then
            Dim resDataRow As ResultDataSet.ResultDataTableRow
            For Each co As CompanySearchResultHit In res.return.companies.hit
                resDataRow = resDataTable.NewRow
                With resDataRow
                    .CompanyId = co.companyResults.companyId
                    .CompanyName = co.companyResults.companyName
                    .Address1 = co.companyResults.address1
                    .City = co.companyResults.city
                    .StateOrProvince = co.companyResults.stateOrProvince
                    .PostalCode = co.companyResults.postalCode
                    .Phone = co.companyResults.hqPhone
                End With
                If Not IsNothing(resDataRow) Then
                    resDataTable.AddResultDataTableRow(resDataRow)
                End If

            Next
        End If
        Return resDataTable
    End Function


    Private Function InsertRecordsODBC(ByVal res As AdvancedCompanySearchResponse) As Boolean
        Dim con As OdbcConnection = GetODBCConnection()
        Dim cmd As OdbcCommand = Nothing
        Dim cmdFind As OdbcCommand = Nothing
        Try
            Dim Sql As String
            Sql = "Insert Into bizData(CompanyId, CompanyName, Address1, City, StateOrProvince, " & _
                  "PostalCode, Phone, offset, LocationType, Duns, ParentDuns, UltimateParentDuns, Sales, DBA, CountryID, LastUpdated) Values (?, ?, ?, ?, " & _
                  "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
            cmd = New OdbcCommand(Sql, con)
            Sql = "Select CompanyId From bizData Where CompanyId=?"
            cmdFind = New OdbcCommand(Sql, con)
            Dim lFindCompanyId As String

            For Each co As CompanySearchResultHit In res.return.companies.hit
                ' Verify if this company is already found in the database.
                With cmdFind
                    .Parameters.Clear()
                    .Parameters.AddWithValue("1", co.companyResults.companyId)

                    lFindCompanyId = cmdFind.ExecuteScalar() & ""
                End With

                ' If this company is not already found, insert it into the database.
                If lFindCompanyId <> co.companyResults.companyId & "" Then
                    With cmd.Parameters
                        .Clear()
                        .AddWithValue("1", co.companyResults.companyId)
                        .AddWithValue("2", co.companyResults.companyName)
                        .AddWithValue("3", co.companyResults.address1)
                        .AddWithValue("4", co.companyResults.city)
                        .AddWithValue("5", co.companyResults.stateOrProvince)
                        .AddWithValue("6", co.companyResults.postalCode)
                        .AddWithValue("7", co.companyResults.hqPhone)
                        .AddWithValue("8", mCurrentOffset)
                        .AddWithValue("9", co.companyResults.locationType)
                        .AddWithValue("10", co.companyResults.duns)
                        .AddWithValue("11", co.companyResults.parentDuns)
                        .AddWithValue("12", co.companyResults.ultimateParentDuns)
                        .AddWithValue("13", co.companyResults.sales)
                        .AddWithValue("14", If(co.companyResults.dbas.Count > 0, co.companyResults.dbas(0).ToString, ""))
                        .AddWithValue("15", co.companyResults.countryId)
                        .AddWithValue("16", Now.ToShortDateString & "  " & Now.ToShortTimeString)
                    End With
                    cmd.ExecuteNonQuery()
                End If
            Next

            Return True
        Catch ex As Exception
            Throw
        Finally
            con.Dispose()
            cmd.Dispose()
            cmdFind.Dispose()
        End Try
    End Function

    Private Function GetODBCConnection() As OdbcConnection
        Try
            Dim con As New OdbcConnection(GetApplicationSetting("ConnectionString"))

            con.Open()
            Return con
        Catch ex As Exception
            Throw
        End Try
    End Function

    Private Function GetApplicationSetting(ByVal SettingName As String) As String
        Return ConfigurationManager.AppSettings(SettingName)
    End Function

    'Public Function getOffset() As Integer
    '    Dim con As OdbcConnection = GetODBCConnection()
    '    Dim queryString As String = _
    '        "SELECT top 1 offset from bizData " _
    '        & "ORDER BY offset DESC;"
    '    Dim command As New OdbcCommand(queryString, con)


    '    mNextOffset = 
    'End Function

    Private Sub InsertRecordsViaTableAdapter(res As AdvancedCompanySearchResponse)
        Dim companyID As String = String.Empty
        Try


            For Each co As CompanySearchResultHit In res.return.companies.hit
                T = T + 1 'this counter = totalHits 45 in this case
            Next 'below the counter hits qty = 1 and ends the sub

            For Each co As CompanySearchResultHit In res.return.companies.hit 'length is length of incoming data string?
                companyID = co.companyResults.companyId

                S = res.return.companies.totalHits
                S = res.return.companies.lastHit
                S = co.hitNumber
                S = co.GetHashCode
                C2 = co.hitNumber 'how can we see value in the debug window? what is the total number of incoming records s/b 200?
                Debug.Print(C2)
                S = res.return.companies.hits
                Dim optionalReturnFields As New GetCompanyDetailOptionalDataRequest
                optionalReturnFields.uniqueId = co.companyResults.companyId
                optionalReturnFields.optionalResponseFields = New OptionalDataResponseFields
                optionalReturnFields.optionalResponseFields.includeIndustries = True
                optionalReturnFields.optionalResponseFields.includeURLs = True
                optionalReturnFields.optionalResponseFields.includeKeyNumbers = True
                optionalReturnFields.optionalResponseFields.includeLocations = True
                optionalReturnFields.optionalResponseFields.includePhones = True
                optionalReturnFields.optionalResponseFields.includeRankings = True
                'optionalReturnFields.optionalResponseFields.includeSocialMedia = True
                optionalReturnFields.optionalResponseFields.includeStocks = True
                optionalReturnFields.optionalResponseFields.includeURLs = True
                Dim optionalResponseFields As New GetCompanyDetailOptionalDataResponse
                optionalResponseFields = Client.GetCompanyDetailOptionalData(APIKey, "", "", optionalReturnFields)

                Dim primaryURLs As String = String.Empty
                Dim fulldescription As String = String.Empty
                Dim companyType As String = String.Empty
                Dim lineOfBusiness As String = String.Empty
                Dim primaryDnBIC As String = String.Empty
                Dim allNAICS As String = String.Empty
                Dim allUSSIC As String = String.Empty
                Dim naics As String = String.Empty
                Dim longitude As String = String.Empty
                Dim latitude As String = String.Empty
                Dim womenOwned As Boolean = Nothing
                Dim minorityOwned As Boolean = Nothing
                Dim ownedOrLeased As String = String.Empty

                If Not optionalResponseFields Is Nothing Then
                    With optionalResponseFields.return
                        primaryURLs = If(optionalResponseFields.return.primaryURLs.Count > 0, optionalResponseFields.return.primaryURLs(0).url, "")
                        fulldescription = If(.fulldescription.Length > 350, Left(.fulldescription, 350), .fulldescription)
                        companyType = .companyType
                        lineOfBusiness = .industries.lineOfBusiness
                        primaryDnBIC = .industries.primaryDnBIC
                        allNAICS = .industries.allNAICS.First.description
                        allUSSIC = .industries.allUSSIC.First.description
                        naics = .industries.allNAICS.First.naics
                        longitude = .locations.First.longitude.ToString
                        latitude = .locations.First.latitude.ToString
                        womenOwned = .womenOwned
                        minorityOwned = .minorityOwned
                        ownedOrLeased = .ownedOrLeased

                    End With

                End If


                C = C + 1

                If co.companyResults.companyId > 0 Then 'Not bizDataDataset.CheckCompanyID(co.companyResults.companyId) > 0 Then
                    'NewRecordCounter = NewRecordCounter + 1
                    'frmDataCollector.lblNewRecordCounter.Text = "Total New Records: " & NewRecordCounter
                    C1 = C1 + 1
                    With co.companyResults
                        bizDataDataset.Insert(.companyId _
                                                     , If(.companyName Is Nothing, "", .companyName) _
                                                     , If(.address1 Is Nothing, "", .address1) _
                                                     , If(.city Is Nothing, "", .city) _
                                                     , If(.stateOrProvince Is Nothing, "", .stateOrProvince) _
                                                     , If(.postalCode Is Nothing, "", .postalCode) _
                                                     , If(.hqPhone Is Nothing, "", .hqPhone) _
                                                     , mCurrentOffset _
                                                     , If(.locationType Is Nothing, "", .locationType) _
                                                     , If(.duns Is Nothing, "", .duns) _
                                                     , If(.parentDuns Is Nothing, "", .parentDuns) _
                                                     , If(.ultimateParentDuns Is Nothing, "", .ultimateParentDuns) _
                                                     , If(.sales, "", .sales) _
                                                     , If(.dbas.Count > 0, .dbas(0).ToString, "") _
                                                     , .countryId _
                                                     , primaryURLs _
                                                     , fulldescription _
                                                     , companyType _
                                                     , String.Empty _
                                                     , String.Empty _
                                                     , lineOfBusiness _
                                                     , primaryDnBIC _
                                                     , allNAICS _
                                                     , allUSSIC _
                                                     , naics _
                                                     , longitude _
                                                     , latitude _
                                                     , womenOwned _
                                                     , minorityOwned _
                                                     , ownedOrLeased _
                                                     , String.Empty _
                                                     , String.Empty _
                                                     , String.Empty _
                                                     , String.Empty _
                                                     , DateTime.Now)
                    End With
                    InsertedCount += 1
                Else
                    With co.companyResults
                        bizDataDataset.UpdateBizData(If(.companyName Is Nothing, "", .companyName) _
                                                     , If(.address1 Is Nothing, "", .address1) _
                                                     , If(.city Is Nothing, "", .city) _
                                                     , If(.stateOrProvince Is Nothing, "", .stateOrProvince) _
                                                     , If(.postalCode Is Nothing, "", .postalCode) _
                                                     , If(.hqPhone Is Nothing, "", .hqPhone) _
                                                     , mCurrentOffset _
                                                     , If(.locationType Is Nothing, "", .locationType) _
                                                     , If(.duns Is Nothing, "", .duns) _
                                                     , If(.parentDuns Is Nothing, "", .parentDuns) _
                                                     , If(.ultimateParentDuns Is Nothing, "", .ultimateParentDuns) _
                                                     , If(.sales, "", .sales) _
                                                     , If(.dbas.Count > 0, .dbas(0).ToString, "") _
                                                     , .countryId _
                                                     , primaryURLs _
                                                     , fulldescription _
                                                     , companyType _
                                                     , String.Empty _
                                                     , String.Empty _
                                                     , lineOfBusiness _
                                                     , primaryDnBIC _
                                                     , allNAICS _
                                                     , allUSSIC _
                                                     , naics _
                                                     , longitude _
                                                     , latitude _
                                                     , womenOwned _
                                                     , minorityOwned _
                                                     , ownedOrLeased _
                                                     , String.Empty _
                                                     , String.Empty _
                                                     , String.Empty _
                                                     , String.Empty _
                                                     , DateTime.Now _
                                                     , .companyId)
                    End With
                    UpdatedCount += 1

                End If

            Next


        Catch ex As Exception
            MsgBox(companyID, MsgBoxStyle.OkOnly)
            Throw
        End Try
    End Sub

    

End Class
